const mysql = require("mysql2/promise");
class Database {
  constructor(config) {
    this.pool = mysql.createPool({
      host: "113.44.189.152",
      user: "root",
      password: "root",
      database: "rouyi",
      port: "3306",
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
      // MySQL2 支持的有效配置选项
      acquireTimeout: 60000, // 获取连接超时时间
      timeout: 60000, // 查询超时时间
      // 连接相关配置
      charset: 'utf8mb4',
      timezone: '+00:00',
    });
    
    // 监听连接错误事件
    this.pool.on('connection', (connection) => {
      console.log('新的数据库连接已建立');
    });
    
    this.pool.on('error', (err) => {
      console.error('数据库连接池错误:', err);
      if (err.code === 'PROTOCOL_CONNECTION_LOST') {
        console.log('检测到连接丢失，将尝试重新连接...');
      }
    });
  }
  async testConnection() {
    try {
      const connection = await this.pool.getConnection();
      connection.release();
      console.log("数据库链接成功!");
      return true;
    } catch (error) {
      console.log("数据库链接失败:", error.message);
      return false;
    }
  }

  /**
   * 检查连接池健康状态
   * @returns {Promise<boolean>} 连接是否健康
   */
  async checkHealth() {
    try {
      const connection = await this.pool.getConnection();
      await connection.ping();
      connection.release();
      return true;
    } catch (error) {
      console.error('数据库健康检查失败:', error);
      return false;
    }
  }

  /**
   * 获取连接池状态信息
   * @returns {Object} 连接池状态
   */
  getPoolStatus() {
    return {
      totalConnections: this.pool._allConnections.length,
      freeConnections: this.pool._freeConnections.length,
      acquiringConnections: this.pool._acquiringConnections.length,
      queuedRequests: this.pool._connectionQueue.length
    };
  }

  /**
   * 执行SQL查询（带重试机制）
   * @param {string} sql - SQL查询语句
   * @param {Array} values - 查询参数
   * @param {number} retries - 重试次数
   * @returns {Promise<Array>} 查询结果
   */
  async query(sql, values, retries = 3) {
    for (let attempt = 1; attempt <= retries; attempt++) {
      try {
        const [rows] = await this.pool.execute(sql, values);
        return rows;
      } catch (error) {
        console.error(`SQL查询错误 (尝试 ${attempt}/${retries}):`, error);
        
        // 如果是连接丢失错误且还有重试机会，则等待后重试
        if (error.code === 'PROTOCOL_CONNECTION_LOST' && attempt < retries) {
          console.log(`连接丢失，等待 ${attempt * 1000}ms 后重试...`);
          await new Promise(resolve => setTimeout(resolve, attempt * 1000));
          continue;
        }
        
        // 如果是其他错误或重试次数用完，则抛出错误
        throw error;
      }
    }
  }

  /**
   * 插入单条记录（带重试机制）
   * @param {string} table - 表名
   * @param {Object} data - 数据对象
   * @param {number} retries - 重试次数
   * @returns {Promise<number>} 插入的ID
   */
  async insert(table, data, retries = 3) {
    for (let attempt = 1; attempt <= retries; attempt++) {
      try {
        const keys = Object.keys(data);
        const values = Object.values(data);
        const placeholders = keys.map(() => "?").join(",");
        const sql = `INSERT INTO ${table} (${keys.join(
          ","
        )}) VALUES (${placeholders})`;

        const [result] = await this.pool.execute(sql, values);
        return result.insertId;
      } catch (error) {
        console.error(`插入数据错误 (尝试 ${attempt}/${retries}):`, error);
        
        // 如果是连接丢失错误且还有重试机会，则等待后重试
        if (error.code === 'PROTOCOL_CONNECTION_LOST' && attempt < retries) {
          console.log(`连接丢失，等待 ${attempt * 1000}ms 后重试...`);
          await new Promise(resolve => setTimeout(resolve, attempt * 1000));
          continue;
        }
        
        // 如果是其他错误或重试次数用完，则抛出错误
        throw error;
      }
    }
  }

  /**
   * 同步执行多个SQL操作（使用事务）
   * @param {Function} callback - 包含多个数据库操作的回调函数
   * @returns {Promise<void>}
   */
  async transaction(callback) {
    const connection = await this.pool.getConnection();

    try {
      await connection.beginTransaction();
      await callback(connection);
      await connection.commit();
    } catch (error) {
      await connection.rollback();
      console.error("事务执行失败:", error);
      throw error;
    } finally {
      connection.release();
    }
  }

  /**
   * 关闭数据库连接池
   */
  async close() {
    await this.pool.end();
  }
}

module.exports = Database;

